package com.ing.mytest.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import com.ing.mytest.model.DSlogline;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

// Here I implement the DAO

public class DSloglineDAOIMPL  implements DSlogDAO {

	private JdbcTemplate jdbcTemplate;
	
	public DSloglineDAOIMPL (DataSource ds) {
		jdbcTemplate = new JdbcTemplate(ds);
	}
	
	@Override
	public DSlogline get(int logID) {
	String sql = "SELECT * FROM log WHERE id=" + logID;
	return (DSlogline) jdbcTemplate.query(sql, new ResultSetExtractor() {
		@Override
		public DSlogline extractData(ResultSet rs) throws SQLException,
			DataAccessException {
			if (rs.next()) {
				DSlogline d = new DSlogline();
				d.setId(rs.getInt("id"));
				d.setTimestamp(rs.getDate("ẗimestamp"));
				d.setSeverity(rs.getString("severity"));
				d.setSource(rs.getString("source"));
				d.setEntity(rs.getString("entity"));
				d.setEntityname(rs.getString("entityname"));
				d.setState(rs.getString("state"));
				d.setServicetypename(rs.getString("servicetypename"));
				d.setSessionid(rs.getInt("sessionid"));
				d.setTaskid(rs.getInt("taskid"));
				return d;
			}
			return null;
		}
	});
	}

	@Override
	public List<DSlogline> list() {
		String sql = "SELECT * from log"
				+ " ORDER BY timestamp DESC"
				+ " LIMIT 100";
		
		
		List<DSlogline> dsll = jdbcTemplate.queryForList(sql); 
		return dsll;
			
		}

	@Override
	public List<String> dsServiceTypeName() {
		String sql = "SELECT DISTINCT servicetypename FROM log";
		List<String> dsservicetypename = jdbcTemplate.queryForList(sql);
		return dsservicetypename;
	}
	
	@Override
	public int dsNumSessions() {
		String sql = " select COUNT(*) FROM (select distinct sessionid from log) AS temp";
		int dsnumsessions = jdbcTemplate.queryForInt(sql);
		return dsnumsessions;
	}

	@Override
	public List<String> dsEngineNames() {
		String sql = "select * FROM (select distinct entityname from log where entity='Engine') AS temp";
		List<String> dsenginenames = jdbcTemplate.queryForList(sql);
		return dsenginenames;
	}

	@Override
	public List<String> dsDriverNames() {
		// TODO Auto-generated method stub
		String sql = "select * FROM (select distinct entityname from log where entity='Driver') AS temp";
		List<String> dsdrivernames = jdbcTemplate.queryForList(sql);
		return dsdrivernames;
	}

	@Override
	public List<String> dsInfoAge() {
		String sql = "SELECT AGE((SELECT MAX(timestamp) FROM log),(SELECT MIN(timestamp) FROM log))";
		List<String> age =  jdbcTemplate.queryForList(sql);
		
		return age;
	}

	@Override
	public List<String> dsHourSum() {
		String sql = "SELECT c.servicetypename, SUM(AGE(c.timestamp, a.timestamp)) "
				+ "from log c "
				+ "inner join log a "
				+ "on c.sessionid = a.sessionid "
				+ "AND "
				+ "c.taskid = a.taskid "
				+ "WHERE c.state='TaskCompleted' "
				+ "AND "
				+ "a.state = 'TaskAccepted' "
				+ "AND "
				+ "(c.timestamp >= "
				+ "(SELECT timestamp FROM log "
				+ "ORDER BY timestamp DESC LIMIT 1) - '1 day'::INTERVAL) "
				+ "GROUP BY c.servicetypename";
		List<String> hourSums = jdbcTemplate.queryForList(sql);
		
		return hourSums;
	}
	
	
	
}
